package fic.storefinder.server.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;

import fic.storefinder.shared.serializable.POI;
import fic.storefinder.shared.serializable.Robe;
import fic.storefinder.shared.utils.Constants;

public class RobeTable extends Table {
	public long getMaxLastUpdateDate() throws SQLException {
		ResultSet rs = null;
		Statement stmt = null;
		try {
			this.openConnection();
			
			
			stmt = this.connection.createStatement();
			rs = stmt.executeQuery("select max(LastUpdateDate) from Robe;");
			Timestamp dbSqlTimestamp = null;
			while (rs.next()) {
				dbSqlTimestamp = rs.getTimestamp("max(LastUpdateDate)");
			}
			
			
			if (dbSqlTimestamp != null) {
				return dbSqlTimestamp.getTime();
			} else {
				return 0;
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			this.closeConection();
		}
		

	}
	
	public ArrayList<HashMap<String, Object>> getRobesToUpdate(long lastUpdateTimestamp) throws SQLException {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			this.openConnection();
			
			String queryStr = "select RobeId, POIId, Price, Currency, ImageUrl, Description, Available, RobeStatus from Robe where LastUpdateDate > ?;";
			pstmt = this.connection.prepareStatement(queryStr);
			pstmt.setTimestamp(1, new Timestamp(lastUpdateTimestamp));
			rs = pstmt.executeQuery(queryStr);
			ArrayList<HashMap<String, Object>> retValue = new ArrayList<HashMap<String, Object>>();
			while (rs.next()) {
				HashMap<String, Object> rowMap = new HashMap<String, Object>();
				rowMap.put(Constants.JSON_KEY_ROBE_ID, rs.getObject(1));
				rowMap.put(Constants.JSON_KEY_ROBE_POIID, rs.getObject(2));
				rowMap.put(Constants.JSON_KEY_ROBE_PRICE, rs.getObject(3));
				rowMap.put(Constants.JSON_KEY_ROBE_CURRENCY, rs.getObject(4));
				rowMap.put(Constants.JSON_KEY_ROBE_IMAGEURL, rs.getObject(5));
				rowMap.put(Constants.JSON_KEY_ROBE_DESCRIPTION, rs.getObject(6));
				rowMap.put(Constants.JSON_KEY_ROBE_AVAILABLE, rs.getObject(7));
				rowMap.put(Constants.JSON_KEY_ROBE_STATUS, rs.getObject(8));
				retValue.add(rowMap);
			}
			
			return retValue;
		} catch (SQLException e) {
			throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			this.closeConection();
		}
		
	}
	
	public void deleteAllRobesForPOI(int poiId) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			this.openConnection();
			
			String queryStr = "UPDATE Robe SET RobeStatus = ?, LastUpdateDate = ? WHERE POIId = ?;";
			pstmt = this.connection.prepareStatement(queryStr);
			pstmt.setInt(1, Constants.ROBESTATUS_DELETED);
			pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			pstmt.setInt(3, poiId);
			pstmt.executeUpdate(queryStr);
			
		} catch (SQLException e) {
			throw e;
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			this.closeConection();
		}
	}

	public ArrayList<Robe> getAllActiveRobeForPOI(POI poi) throws SQLException {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			this.openConnection();
			
			String queryStr = "select RobeId, Price, Currency, ImageUrl, Description, Available  from Robe where POIId = ? AND RobeStatus = ? ;";
			pstmt = this.connection.prepareStatement(queryStr);
			pstmt.setInt(1, poi.getPoiId());
			pstmt.setInt(2, Constants.ROBESTATUS_ACTIVE);
			rs = pstmt.executeQuery(queryStr);
			ArrayList<Robe> retValue = new ArrayList<Robe>();
			while (rs.next()) {
				Robe robe = new Robe();
				robe.setRobeId(rs.getInt(1));
				robe.setPrice(rs.getInt(2));
				robe.setCurrency(rs.getString(3));
				robe.setImageUrl(rs.getString(4));
				robe.setDescription(rs.getString(5));
				robe.setAvailable(rs.getInt(6));
				retValue.add(robe);
			}
			return retValue;
		} catch (SQLException e) {
			throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			
			this.closeConection();
		}
	}

	

	public void updateRobe(Robe robe) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			this.openConnection();
			
			String queryStr = "UPDATE Robe SET Price = ?, Currency = ?, Description = ?, Available = ?, LastUpdateDate = ?  WHERE RobeId = ?;";
			pstmt = this.connection.prepareStatement(queryStr);
			pstmt.setInt(1, robe.getPrice());
			pstmt.setString(2, robe.getCurrency());
			pstmt.setString(3, robe.getDescription());
			pstmt.setInt(4, robe.getAvailable());
			pstmt.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
			pstmt.setInt(6, robe.getRobeId());

			pstmt.executeUpdate(queryStr);	
			
		} catch (SQLException e) {
			throw e;
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			this.closeConection();
		}
	}
	
	public void insertRobe(Robe robe) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			this.openConnection();
			
			String queryStr = "INSERT INTO Robe (POIId, Price, Currency, Description, Available, LastUpdateDate, RobeStatus)  VALUES(?, ?, ?, ?, ?, ?, ?);";
			pstmt = this.connection.prepareStatement(queryStr);
			pstmt.setInt(1, robe.getPoiId());
			pstmt.setInt(2, robe.getPrice());
			pstmt.setString(3, robe.getCurrency());
			pstmt.setString(4, robe.getDescription());
			pstmt.setInt(5, robe.getAvailable());
			pstmt.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
			pstmt.setInt(7, Constants.ROBESTATUS_ACTIVE);

			pstmt.executeUpdate(queryStr);	
			
		} catch (SQLException e) {
			throw e;
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			this.closeConection();
		}
	}
	
	public void deleteRobe(Robe robe) throws SQLException {
		PreparedStatement pstmt = null;
		try {
			this.openConnection();
			
			String queryStr = "UPDATE Robe SET RobeStatus = ?, LastUpdateDate = ? WHERE RobeId = ?;";
			pstmt = this.connection.prepareStatement(queryStr);
			pstmt.setInt(1, Constants.ROBESTATUS_DELETED);
			pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			pstmt.setInt(3, robe.getRobeId());
			pstmt.executeUpdate(queryStr);
			
		} catch (SQLException e) {
			throw e;
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			this.closeConection();
		}
	}
	
	
}
